{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b1653f3e-eb12-4d0a-b5ae-d073a0786b71",
   "metadata": {},
   "outputs": [],
   "source": [
    "import polars as pl\n",
    "from perspective.widget import PerspectiveWidget"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "5c3ee154-59d6-4740-937f-c7ba7d5ea0eb",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (363, 18)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>序号</th><th>券商</th><th>交易日期</th><th>交易时间</th><th>证券代码</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>发生金额</th><th>手续费率</th><th>印花税率</th><th>过户费率</th></tr><tr><td>u32</td><td>str</td><td>date</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>1</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:33:37&quot;</td><td>&quot;000900&quot;</td><td>&quot;现代投资&quot;</td><td>&quot;买入&quot;</td><td>4.05</td><td>34400.0</td><td>139320.0</td><td>22.29</td><td>0.0</td><td>1.39</td><td>0.0</td><td>-139342.29</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>2</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:34:24&quot;</td><td>&quot;601077&quot;</td><td>&quot;渝农商行&quot;</td><td>&quot;买入&quot;</td><td>3.65</td><td>38300.0</td><td>139795.0</td><td>22.37</td><td>0.0</td><td>1.38</td><td>0.0</td><td>-139818.75</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>3</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:36:30&quot;</td><td>&quot;600894&quot;</td><td>&quot;广日股份&quot;</td><td>&quot;买入&quot;</td><td>6.54</td><td>21400.0</td><td>139956.0</td><td>22.39</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-139979.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>4</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:37:25&quot;</td><td>&quot;601992&quot;</td><td>&quot;金隅集团&quot;</td><td>&quot;买入&quot;</td><td>2.59</td><td>54000.0</td><td>139860.0</td><td>22.38</td><td>0.0</td><td>1.42</td><td>0.0</td><td>-139883.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>5</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:38:16&quot;</td><td>&quot;002462&quot;</td><td>&quot;嘉事堂&quot;</td><td>&quot;买入&quot;</td><td>13.51</td><td>10400.0</td><td>140504.0</td><td>22.48</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-140526.48</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>359</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:31:53&quot;</td><td>&quot;002956&quot;</td><td>&quot;西麦食品&quot;</td><td>&quot;卖出&quot;</td><td>14.13</td><td>5000.0</td><td>70650.0</td><td>6.74</td><td>35.35</td><td>0.0</td><td>0.0</td><td>70607.91</td><td>0.000095</td><td>0.0005</td><td>0.0</td></tr><tr><td>360</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:39:57&quot;</td><td>&quot;603214&quot;</td><td>&quot;爱婴室&quot;</td><td>&quot;买入&quot;</td><td>15.84</td><td>3100.0</td><td>49104.0</td><td>5.0</td><td>0.0</td><td>0.51</td><td>0.0</td><td>-49109.51</td><td>0.000102</td><td>0.0</td><td>0.00001</td></tr><tr><td>361</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:40:55&quot;</td><td>&quot;300132&quot;</td><td>&quot;青松股份&quot;</td><td>&quot;买入&quot;</td><td>5.21</td><td>9600.0</td><td>50016.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-50021.0</td><td>0.0001</td><td>0.0</td><td>0.0</td></tr><tr><td>362</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:43:13&quot;</td><td>&quot;002492&quot;</td><td>&quot;恒基达鑫&quot;</td><td>&quot;买入&quot;</td><td>5.91</td><td>8400.0</td><td>49644.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-49649.0</td><td>0.000101</td><td>0.0</td><td>0.0</td></tr><tr><td>363</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:44:45&quot;</td><td>&quot;002111&quot;</td><td>&quot;威海广泰&quot;</td><td>&quot;买入&quot;</td><td>9.24</td><td>5400.0</td><td>49896.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-49901.0</td><td>0.0001</td><td>0.0</td><td>0.0</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (363, 18)\n",
       "┌──────┬──────────┬────────────┬──────────┬───┬────────────┬──────────┬──────────┬──────────┐\n",
       "│ 序号 ┆ 券商     ┆ 交易日期   ┆ 交易时间 ┆ … ┆ 发生金额   ┆ 手续费率 ┆ 印花税率 ┆ 过户费率 │\n",
       "│ ---  ┆ ---      ┆ ---        ┆ ---      ┆   ┆ ---        ┆ ---      ┆ ---      ┆ ---      │\n",
       "│ u32  ┆ str      ┆ date       ┆ str      ┆   ┆ f64        ┆ f64      ┆ f64      ┆ f64      │\n",
       "╞══════╪══════════╪════════════╪══════════╪═══╪════════════╪══════════╪══════════╪══════════╡\n",
       "│ 1    ┆ 湘财     ┆ 2022-07-11 ┆ 09:33:37 ┆ … ┆ -139342.29 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 2    ┆ 湘财     ┆ 2022-07-11 ┆ 09:34:24 ┆ … ┆ -139818.75 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 3    ┆ 湘财     ┆ 2022-07-11 ┆ 09:36:30 ┆ … ┆ -139979.8  ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 4    ┆ 湘财     ┆ 2022-07-11 ┆ 09:37:25 ┆ … ┆ -139883.8  ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 5    ┆ 湘财     ┆ 2022-07-11 ┆ 09:38:16 ┆ … ┆ -140526.48 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ …    ┆ …        ┆ …          ┆ …        ┆ … ┆ …          ┆ …        ┆ …        ┆ …        │\n",
       "│ 359  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:31:53 ┆ … ┆ 70607.91   ┆ 0.000095 ┆ 0.0005   ┆ 0.0      │\n",
       "│ 360  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:39:57 ┆ … ┆ -49109.51  ┆ 0.000102 ┆ 0.0      ┆ 0.00001  │\n",
       "│ 361  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:40:55 ┆ … ┆ -50021.0   ┆ 0.0001   ┆ 0.0      ┆ 0.0      │\n",
       "│ 362  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:43:13 ┆ … ┆ -49649.0   ┆ 0.000101 ┆ 0.0      ┆ 0.0      │\n",
       "│ 363  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:44:45 ┆ … ┆ -49901.0   ┆ 0.0001   ┆ 0.0      ┆ 0.0      │\n",
       "└──────┴──────────┴────────────┴──────────┴───┴────────────┴──────────┴──────────┴──────────┘"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pl.read_parquet(\"stock_tradesparquet\")\n",
    "df = df.sort(\"交易日期\", \"交易时间\", \"证券代码\")\n",
    "df = df.with_columns(\n",
    "    pl.col(\"交易时间\").cast(pl.String),\n",
    "    手续费率=pl.col(\"手续费\") / pl.col(\"成交金额\"),\n",
    "    印花税率=pl.col(\"印花税\") / pl.col(\"成交金额\"),\n",
    "    过户费率=pl.col(\"过户费\") / pl.col(\"成交金额\"),\n",
    ")\n",
    "df = df.with_row_index(\"序号\", 1)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "e2281de4-c74e-422e-a371-bd60ec07bb50",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "01c797733f924d9982439f3f2f7c0ecd",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "PerspectiveWidget(binding_mode='server', columns=['序号', '券商', '交易日期', '交易时间', '证券代码', '证券名称', '买卖标志', '成交价格', …"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PerspectiveWidget(df)  # 将df传入"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "7c47a0f9-30fa-4982-9ceb-46e5d2a290c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "d1 = df.join(\n",
    "    df.group_by(\"证券代码\", \"证券名称\")\n",
    "    .agg(\n",
    "        结余数量=(\n",
    "            pl.when(pl.col(\"买卖标志\") == \"卖出\")\n",
    "            .then(-pl.col(\"成交数量\"))\n",
    "            .when(pl.col(\"买卖标志\") == \"买入\")\n",
    "            .then(pl.col(\"成交数量\"))\n",
    "            .sum()\n",
    "        ),\n",
    "    )\n",
    "    .filter(pl.col(\"结余数量\") < 0),  # filter()准备剔除的;以上是join()的第一个参数\n",
    "    on=\"证券代码\",\n",
    "    how=\"anti\",\n",
    ")\n",
    "# group_by 先分组\n",
    "# agg再汇总"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "43e7f69d-05ee-4d42-b30c-adfa333f280e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (363, 18)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>序号</th><th>券商</th><th>交易日期</th><th>交易时间</th><th>证券代码</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>发生金额</th><th>手续费率</th><th>印花税率</th><th>过户费率</th></tr><tr><td>u32</td><td>str</td><td>date</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>1</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:33:37&quot;</td><td>&quot;000900&quot;</td><td>&quot;现代投资&quot;</td><td>&quot;买入&quot;</td><td>4.05</td><td>34400.0</td><td>139320.0</td><td>22.29</td><td>0.0</td><td>1.39</td><td>0.0</td><td>-139342.29</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>2</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:34:24&quot;</td><td>&quot;601077&quot;</td><td>&quot;渝农商行&quot;</td><td>&quot;买入&quot;</td><td>3.65</td><td>38300.0</td><td>139795.0</td><td>22.37</td><td>0.0</td><td>1.38</td><td>0.0</td><td>-139818.75</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>3</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:36:30&quot;</td><td>&quot;600894&quot;</td><td>&quot;广日股份&quot;</td><td>&quot;买入&quot;</td><td>6.54</td><td>21400.0</td><td>139956.0</td><td>22.39</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-139979.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>4</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:37:25&quot;</td><td>&quot;601992&quot;</td><td>&quot;金隅集团&quot;</td><td>&quot;买入&quot;</td><td>2.59</td><td>54000.0</td><td>139860.0</td><td>22.38</td><td>0.0</td><td>1.42</td><td>0.0</td><td>-139883.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>5</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:38:16&quot;</td><td>&quot;002462&quot;</td><td>&quot;嘉事堂&quot;</td><td>&quot;买入&quot;</td><td>13.51</td><td>10400.0</td><td>140504.0</td><td>22.48</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-140526.48</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>359</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:31:53&quot;</td><td>&quot;002956&quot;</td><td>&quot;西麦食品&quot;</td><td>&quot;卖出&quot;</td><td>14.13</td><td>5000.0</td><td>70650.0</td><td>6.74</td><td>35.35</td><td>0.0</td><td>0.0</td><td>70607.91</td><td>0.000095</td><td>0.0005</td><td>0.0</td></tr><tr><td>360</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:39:57&quot;</td><td>&quot;603214&quot;</td><td>&quot;爱婴室&quot;</td><td>&quot;买入&quot;</td><td>15.84</td><td>3100.0</td><td>49104.0</td><td>5.0</td><td>0.0</td><td>0.51</td><td>0.0</td><td>-49109.51</td><td>0.000102</td><td>0.0</td><td>0.00001</td></tr><tr><td>361</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:40:55&quot;</td><td>&quot;300132&quot;</td><td>&quot;青松股份&quot;</td><td>&quot;买入&quot;</td><td>5.21</td><td>9600.0</td><td>50016.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-50021.0</td><td>0.0001</td><td>0.0</td><td>0.0</td></tr><tr><td>362</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:43:13&quot;</td><td>&quot;002492&quot;</td><td>&quot;恒基达鑫&quot;</td><td>&quot;买入&quot;</td><td>5.91</td><td>8400.0</td><td>49644.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-49649.0</td><td>0.000101</td><td>0.0</td><td>0.0</td></tr><tr><td>363</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:44:45&quot;</td><td>&quot;002111&quot;</td><td>&quot;威海广泰&quot;</td><td>&quot;买入&quot;</td><td>9.24</td><td>5400.0</td><td>49896.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-49901.0</td><td>0.0001</td><td>0.0</td><td>0.0</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (363, 18)\n",
       "┌──────┬──────────┬────────────┬──────────┬───┬────────────┬──────────┬──────────┬──────────┐\n",
       "│ 序号 ┆ 券商     ┆ 交易日期   ┆ 交易时间 ┆ … ┆ 发生金额   ┆ 手续费率 ┆ 印花税率 ┆ 过户费率 │\n",
       "│ ---  ┆ ---      ┆ ---        ┆ ---      ┆   ┆ ---        ┆ ---      ┆ ---      ┆ ---      │\n",
       "│ u32  ┆ str      ┆ date       ┆ str      ┆   ┆ f64        ┆ f64      ┆ f64      ┆ f64      │\n",
       "╞══════╪══════════╪════════════╪══════════╪═══╪════════════╪══════════╪══════════╪══════════╡\n",
       "│ 1    ┆ 湘财     ┆ 2022-07-11 ┆ 09:33:37 ┆ … ┆ -139342.29 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 2    ┆ 湘财     ┆ 2022-07-11 ┆ 09:34:24 ┆ … ┆ -139818.75 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 3    ┆ 湘财     ┆ 2022-07-11 ┆ 09:36:30 ┆ … ┆ -139979.8  ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 4    ┆ 湘财     ┆ 2022-07-11 ┆ 09:37:25 ┆ … ┆ -139883.8  ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 5    ┆ 湘财     ┆ 2022-07-11 ┆ 09:38:16 ┆ … ┆ -140526.48 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ …    ┆ …        ┆ …          ┆ …        ┆ … ┆ …          ┆ …        ┆ …        ┆ …        │\n",
       "│ 359  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:31:53 ┆ … ┆ 70607.91   ┆ 0.000095 ┆ 0.0005   ┆ 0.0      │\n",
       "│ 360  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:39:57 ┆ … ┆ -49109.51  ┆ 0.000102 ┆ 0.0      ┆ 0.00001  │\n",
       "│ 361  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:40:55 ┆ … ┆ -50021.0   ┆ 0.0001   ┆ 0.0      ┆ 0.0      │\n",
       "│ 362  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:43:13 ┆ … ┆ -49649.0   ┆ 0.000101 ┆ 0.0      ┆ 0.0      │\n",
       "│ 363  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:44:45 ┆ … ┆ -49901.0   ┆ 0.0001   ┆ 0.0      ┆ 0.0      │\n",
       "└──────┴──────────┴────────────┴──────────┴───┴────────────┴──────────┴──────────┴──────────┘"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "e9adc4ed-c313-4608-b2d1-e8030ad1311b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (358, 18)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>序号</th><th>券商</th><th>交易日期</th><th>交易时间</th><th>证券代码</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>发生金额</th><th>手续费率</th><th>印花税率</th><th>过户费率</th></tr><tr><td>u32</td><td>str</td><td>date</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>1</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:33:37&quot;</td><td>&quot;000900&quot;</td><td>&quot;现代投资&quot;</td><td>&quot;买入&quot;</td><td>4.05</td><td>34400.0</td><td>139320.0</td><td>22.29</td><td>0.0</td><td>1.39</td><td>0.0</td><td>-139342.29</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>2</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:34:24&quot;</td><td>&quot;601077&quot;</td><td>&quot;渝农商行&quot;</td><td>&quot;买入&quot;</td><td>3.65</td><td>38300.0</td><td>139795.0</td><td>22.37</td><td>0.0</td><td>1.38</td><td>0.0</td><td>-139818.75</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>3</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:36:30&quot;</td><td>&quot;600894&quot;</td><td>&quot;广日股份&quot;</td><td>&quot;买入&quot;</td><td>6.54</td><td>21400.0</td><td>139956.0</td><td>22.39</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-139979.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>4</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:37:25&quot;</td><td>&quot;601992&quot;</td><td>&quot;金隅集团&quot;</td><td>&quot;买入&quot;</td><td>2.59</td><td>54000.0</td><td>139860.0</td><td>22.38</td><td>0.0</td><td>1.42</td><td>0.0</td><td>-139883.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>5</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:38:16&quot;</td><td>&quot;002462&quot;</td><td>&quot;嘉事堂&quot;</td><td>&quot;买入&quot;</td><td>13.51</td><td>10400.0</td><td>140504.0</td><td>22.48</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-140526.48</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>359</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:31:53&quot;</td><td>&quot;002956&quot;</td><td>&quot;西麦食品&quot;</td><td>&quot;卖出&quot;</td><td>14.13</td><td>5000.0</td><td>70650.0</td><td>6.74</td><td>35.35</td><td>0.0</td><td>0.0</td><td>70607.91</td><td>0.000095</td><td>0.0005</td><td>0.0</td></tr><tr><td>360</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:39:57&quot;</td><td>&quot;603214&quot;</td><td>&quot;爱婴室&quot;</td><td>&quot;买入&quot;</td><td>15.84</td><td>3100.0</td><td>49104.0</td><td>5.0</td><td>0.0</td><td>0.51</td><td>0.0</td><td>-49109.51</td><td>0.000102</td><td>0.0</td><td>0.00001</td></tr><tr><td>361</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:40:55&quot;</td><td>&quot;300132&quot;</td><td>&quot;青松股份&quot;</td><td>&quot;买入&quot;</td><td>5.21</td><td>9600.0</td><td>50016.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-50021.0</td><td>0.0001</td><td>0.0</td><td>0.0</td></tr><tr><td>362</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:43:13&quot;</td><td>&quot;002492&quot;</td><td>&quot;恒基达鑫&quot;</td><td>&quot;买入&quot;</td><td>5.91</td><td>8400.0</td><td>49644.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-49649.0</td><td>0.000101</td><td>0.0</td><td>0.0</td></tr><tr><td>363</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:44:45&quot;</td><td>&quot;002111&quot;</td><td>&quot;威海广泰&quot;</td><td>&quot;买入&quot;</td><td>9.24</td><td>5400.0</td><td>49896.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-49901.0</td><td>0.0001</td><td>0.0</td><td>0.0</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (358, 18)\n",
       "┌──────┬──────────┬────────────┬──────────┬───┬────────────┬──────────┬──────────┬──────────┐\n",
       "│ 序号 ┆ 券商     ┆ 交易日期   ┆ 交易时间 ┆ … ┆ 发生金额   ┆ 手续费率 ┆ 印花税率 ┆ 过户费率 │\n",
       "│ ---  ┆ ---      ┆ ---        ┆ ---      ┆   ┆ ---        ┆ ---      ┆ ---      ┆ ---      │\n",
       "│ u32  ┆ str      ┆ date       ┆ str      ┆   ┆ f64        ┆ f64      ┆ f64      ┆ f64      │\n",
       "╞══════╪══════════╪════════════╪══════════╪═══╪════════════╪══════════╪══════════╪══════════╡\n",
       "│ 1    ┆ 湘财     ┆ 2022-07-11 ┆ 09:33:37 ┆ … ┆ -139342.29 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 2    ┆ 湘财     ┆ 2022-07-11 ┆ 09:34:24 ┆ … ┆ -139818.75 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 3    ┆ 湘财     ┆ 2022-07-11 ┆ 09:36:30 ┆ … ┆ -139979.8  ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 4    ┆ 湘财     ┆ 2022-07-11 ┆ 09:37:25 ┆ … ┆ -139883.8  ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 5    ┆ 湘财     ┆ 2022-07-11 ┆ 09:38:16 ┆ … ┆ -140526.48 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ …    ┆ …        ┆ …          ┆ …        ┆ … ┆ …          ┆ …        ┆ …        ┆ …        │\n",
       "│ 359  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:31:53 ┆ … ┆ 70607.91   ┆ 0.000095 ┆ 0.0005   ┆ 0.0      │\n",
       "│ 360  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:39:57 ┆ … ┆ -49109.51  ┆ 0.000102 ┆ 0.0      ┆ 0.00001  │\n",
       "│ 361  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:40:55 ┆ … ┆ -50021.0   ┆ 0.0001   ┆ 0.0      ┆ 0.0      │\n",
       "│ 362  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:43:13 ┆ … ┆ -49649.0   ┆ 0.000101 ┆ 0.0      ┆ 0.0      │\n",
       "│ 363  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:44:45 ┆ … ┆ -49901.0   ┆ 0.0001   ┆ 0.0      ┆ 0.0      │\n",
       "└──────┴──────────┴────────────┴──────────┴───┴────────────┴──────────┴──────────┴──────────┘"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "4b31d5cc-dc09-4270-b63d-2d3ffeb7cdc5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.date(2022, 7, 11)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "start_date = df[\"交易日期\"].min()\n",
    "start_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "9da6ef8a-1b0a-421e-a082-8a6a7f98f2a6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.date(2023, 10, 31)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "end_date = df[\"交易日期\"].max()\n",
    "end_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "464f658d-60f2-4be7-9c50-f6c857700bd1",
   "metadata": {},
   "outputs": [],
   "source": [
    "k1 = pl.select(日期=pl.date_range(start_date, end_date))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "105e1bf1-d82a-450c-90f7-a1e419e664b6",
   "metadata": {},
   "outputs": [],
   "source": [
    "k2 = df[\"证券代码\"].unique().sort().to_frame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "e33f74d5-180c-46cc-973a-73b342a34895",
   "metadata": {},
   "outputs": [],
   "source": [
    "k = k1.join(k2, how=\"cross\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "54025e05-130e-482f-a5bb-29ced8ee2d17",
   "metadata": {},
   "outputs": [],
   "source": [
    "d2 = (\n",
    "    k.join(\n",
    "        d1, left_on=[\"日期\", \"证券代码\"], right_on=[\"交易日期\", \"证券代码\"], how=\"left\"\n",
    "    )\n",
    "    .sort(\"日期\", \"证券代码\")\n",
    "    .with_columns(\n",
    "        结余数量=(\n",
    "            pl.when(pl.col(\"买卖标志\") == \"买入\")\n",
    "            .then(pl.col(\"成交数量\"))\n",
    "            .when(pl.col(\"买卖标志\") == \"卖出\")\n",
    "            .then(-pl.col(\"成交数量\"))\n",
    "            .otherwise(0)\n",
    "            .cum_sum()\n",
    "            .over(\"证券代码\")\n",
    "        ),\n",
    "    )\n",
    "    .filter(pl.col.结余数量 > 0)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "c872241d-3904-4c27-bc23-677100201a6c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "f0f1054256184246a239092ca6b7bacd",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "PerspectiveWidget(binding_mode='server', columns=['日期', '证券代码', '序号', '券商', '交易时间', '证券名称', '买卖标志', '成交价格', '成…"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PerspectiveWidget(d2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "95b55d23-4152-4560-8bbc-97a324199967",
   "metadata": {},
   "outputs": [],
   "source": [
    "import tushare as ts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "cf745ad4-3d6e-4892-879e-a3c34ba2afaa",
   "metadata": {},
   "outputs": [],
   "source": [
    "pro = ts.pro_api()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "4b3a1c23-6809-463f-9f09-ec195079ef07",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (318, 11)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ts_code</th><th>trade_date</th><th>open</th><th>high</th><th>low</th><th>close</th><th>pre_close</th><th>change</th><th>pct_chg</th><th>vol</th><th>amount</th></tr><tr><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;002642.SZ&quot;</td><td>&quot;20231031&quot;</td><td>9.15</td><td>9.26</td><td>8.82</td><td>8.9</td><td>9.15</td><td>-0.25</td><td>-2.7322</td><td>547036.5</td><td>492268.316</td></tr><tr><td>&quot;002642.SZ&quot;</td><td>&quot;20231030&quot;</td><td>8.89</td><td>9.19</td><td>8.82</td><td>9.15</td><td>9.11</td><td>0.04</td><td>0.4391</td><td>559689.5</td><td>506339.252</td></tr><tr><td>&quot;002642.SZ&quot;</td><td>&quot;20231027&quot;</td><td>9.54</td><td>9.54</td><td>8.97</td><td>9.11</td><td>9.54</td><td>-0.43</td><td>-4.5073</td><td>828304.5</td><td>758040.447</td></tr><tr><td>&quot;002642.SZ&quot;</td><td>&quot;20231026&quot;</td><td>9.5</td><td>9.88</td><td>9.42</td><td>9.54</td><td>9.7</td><td>-0.16</td><td>-1.6495</td><td>952196.0</td><td>917587.922</td></tr><tr><td>&quot;002642.SZ&quot;</td><td>&quot;20231025&quot;</td><td>9.38</td><td>9.75</td><td>9.25</td><td>9.7</td><td>9.3</td><td>0.4</td><td>4.3011</td><td>1.0005e6</td><td>960635.584</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>&quot;002642.SZ&quot;</td><td>&quot;20220715&quot;</td><td>5.77</td><td>5.79</td><td>5.62</td><td>5.64</td><td>5.79</td><td>-0.15</td><td>-2.5907</td><td>169578.91</td><td>96321.611</td></tr><tr><td>&quot;002642.SZ&quot;</td><td>&quot;20220714&quot;</td><td>5.9</td><td>5.9</td><td>5.78</td><td>5.79</td><td>5.9</td><td>-0.11</td><td>-1.8644</td><td>159665.0</td><td>92891.23</td></tr><tr><td>&quot;002642.SZ&quot;</td><td>&quot;20220713&quot;</td><td>5.74</td><td>5.91</td><td>5.71</td><td>5.9</td><td>5.71</td><td>0.19</td><td>3.3275</td><td>235737.99</td><td>137809.926</td></tr><tr><td>&quot;002642.SZ&quot;</td><td>&quot;20220712&quot;</td><td>5.7</td><td>5.73</td><td>5.65</td><td>5.71</td><td>5.68</td><td>0.03</td><td>0.5282</td><td>133403.12</td><td>76014.57</td></tr><tr><td>&quot;002642.SZ&quot;</td><td>&quot;20220711&quot;</td><td>5.78</td><td>5.83</td><td>5.63</td><td>5.68</td><td>5.78</td><td>-0.1</td><td>-1.7301</td><td>175923.04</td><td>100352.055</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (318, 11)\n",
       "┌───────────┬────────────┬──────┬──────┬───┬────────┬─────────┬───────────┬────────────┐\n",
       "│ ts_code   ┆ trade_date ┆ open ┆ high ┆ … ┆ change ┆ pct_chg ┆ vol       ┆ amount     │\n",
       "│ ---       ┆ ---        ┆ ---  ┆ ---  ┆   ┆ ---    ┆ ---     ┆ ---       ┆ ---        │\n",
       "│ str       ┆ str        ┆ f64  ┆ f64  ┆   ┆ f64    ┆ f64     ┆ f64       ┆ f64        │\n",
       "╞═══════════╪════════════╪══════╪══════╪═══╪════════╪═════════╪═══════════╪════════════╡\n",
       "│ 002642.SZ ┆ 20231031   ┆ 9.15 ┆ 9.26 ┆ … ┆ -0.25  ┆ -2.7322 ┆ 547036.5  ┆ 492268.316 │\n",
       "│ 002642.SZ ┆ 20231030   ┆ 8.89 ┆ 9.19 ┆ … ┆ 0.04   ┆ 0.4391  ┆ 559689.5  ┆ 506339.252 │\n",
       "│ 002642.SZ ┆ 20231027   ┆ 9.54 ┆ 9.54 ┆ … ┆ -0.43  ┆ -4.5073 ┆ 828304.5  ┆ 758040.447 │\n",
       "│ 002642.SZ ┆ 20231026   ┆ 9.5  ┆ 9.88 ┆ … ┆ -0.16  ┆ -1.6495 ┆ 952196.0  ┆ 917587.922 │\n",
       "│ 002642.SZ ┆ 20231025   ┆ 9.38 ┆ 9.75 ┆ … ┆ 0.4    ┆ 4.3011  ┆ 1.0005e6  ┆ 960635.584 │\n",
       "│ …         ┆ …          ┆ …    ┆ …    ┆ … ┆ …      ┆ …       ┆ …         ┆ …          │\n",
       "│ 002642.SZ ┆ 20220715   ┆ 5.77 ┆ 5.79 ┆ … ┆ -0.15  ┆ -2.5907 ┆ 169578.91 ┆ 96321.611  │\n",
       "│ 002642.SZ ┆ 20220714   ┆ 5.9  ┆ 5.9  ┆ … ┆ -0.11  ┆ -1.8644 ┆ 159665.0  ┆ 92891.23   │\n",
       "│ 002642.SZ ┆ 20220713   ┆ 5.74 ┆ 5.91 ┆ … ┆ 0.19   ┆ 3.3275  ┆ 235737.99 ┆ 137809.926 │\n",
       "│ 002642.SZ ┆ 20220712   ┆ 5.7  ┆ 5.73 ┆ … ┆ 0.03   ┆ 0.5282  ┆ 133403.12 ┆ 76014.57   │\n",
       "│ 002642.SZ ┆ 20220711   ┆ 5.78 ┆ 5.83 ┆ … ┆ -0.1   ┆ -1.7301 ┆ 175923.04 ┆ 100352.055 │\n",
       "└───────────┴────────────┴──────┴──────┴───┴────────┴─────────┴───────────┴────────────┘"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hq = pro.daily(\n",
    "    ts_code=\"002642.SZ\",\n",
    "    start_date=format(start_date, \"%Y%m%d\"),\n",
    "    end_date=format(end_date, \"%Y%m%d\"),\n",
    ")\n",
    "hq = pl.from_pandas(hq)\n",
    "hq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "2f35c422-5c27-454c-9bea-5d8db1009e83",
   "metadata": {},
   "outputs": [],
   "source": [
    "ts_code = (\n",
    "    d1.select(\n",
    "        证券代码2=(\n",
    "            pl.when(pl.col(\"证券代码\").str.head(1).is_in([\"0\", \"3\"]))\n",
    "            .then(pl.format(\"{}.SZ\", pl.col(\"证券代码\")))\n",
    "            .when(pl.col(\"证券代码\").str.head(1).is_in([\"6\"]))\n",
    "            .then(pl.format(\"{}.SH\", pl.col(\"证券代码\")))\n",
    "        ),\n",
    "    )\n",
    "    .to_series()\n",
    "    .unique()\n",
    "    .sort()\n",
    "    .to_list()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "52b41026-c460-4a05-b04c-7ee898a55dc5",
   "metadata": {},
   "outputs": [],
   "source": [
    "from tqdm.notebook import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "2f9f3bdf-c4a8-4803-a1a0-cd84c1e9a23e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "ca33df9029ec4ecfa9af48c71361e5d1",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "  0%|          | 0/149 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "hq = [\n",
    "    pl.from_pandas(\n",
    "        pro.daily(\n",
    "            ts_code=ts_code,\n",
    "            start_date=format(start_date, \"%Y%m%d\"),\n",
    "            end_date=format(end_date, \"%Y%m%d\"),\n",
    "        )\n",
    "    )\n",
    "    for ts_code in tqdm(ts_code)\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "f5024737-dacf-471d-bfe2-442d5c6c7035",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame,\n",
       " polars.dataframe.frame.DataFrame]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[type(i) for i in hq]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "c6825e6d-d453-41b2-a72c-3e425b44476c",
   "metadata": {},
   "outputs": [],
   "source": [
    "hq = pl.concat(hq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "2c2a27a8-f434-46fa-b500-0406dd126cec",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/c/Users/smile/repo/week08\n"
     ]
    }
   ],
   "source": [
    "!pwd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "86c4351c-3403-457a-b32c-a9acac475136",
   "metadata": {},
   "outputs": [],
   "source": [
    "hq.write_parquet(\"daily.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "bb29f758-a3fc-44ae-8117-d95edab47994",
   "metadata": {},
   "outputs": [],
   "source": [
    "hq = pl.read_parquet(\"daily.parquet\")\n",
    "hq = hq.with_columns(\n",
    "    pl.col(\"ts_code\").str.head(6),\n",
    "    pl.col(\"trade_date\").str.to_date(\"%Y%m%d\"),\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "da8a73a3-1147-4620-bc2e-691b2c01ca21",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (358, 28)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>序号</th><th>券商</th><th>交易日期</th><th>交易时间</th><th>证券代码</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>发生金额</th><th>手续费率</th><th>印花税率</th><th>过户费率</th><th>open</th><th>high</th><th>low</th><th>close</th><th>pre_close</th><th>change</th><th>pct_chg</th><th>vol</th><th>amount</th><th>vlratio</th></tr><tr><td>u32</td><td>str</td><td>date</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>237</td><td>&quot;湘财&quot;</td><td>2023-05-09</td><td>&quot;09:33:16&quot;</td><td>&quot;601166&quot;</td><td>&quot;兴业银行&quot;</td><td>&quot;卖出&quot;</td><td>17.75</td><td>3000.0</td><td>53250.0</td><td>8.52</td><td>53.25</td><td>0.53</td><td>0.0</td><td>53187.7</td><td>0.00016</td><td>0.001</td><td>0.00001</td><td>17.77</td><td>17.94</td><td>17.5</td><td>17.56</td><td>17.63</td><td>-0.07</td><td>-0.3971</td><td>1.6276e6</td><td>2.8810e6</td><td>0.000018</td></tr><tr><td>48</td><td>&quot;湘财&quot;</td><td>2022-10-25</td><td>&quot;09:30:19&quot;</td><td>&quot;300368&quot;</td><td>&quot;汇金股份&quot;</td><td>&quot;卖出&quot;</td><td>6.2</td><td>100.0</td><td>620.0</td><td>0.1</td><td>0.62</td><td>0.01</td><td>0.0</td><td>619.28</td><td>0.000161</td><td>0.001</td><td>0.000016</td><td>6.18</td><td>6.25</td><td>5.95</td><td>6.06</td><td>6.2</td><td>-0.14</td><td>-2.2581</td><td>49219.99</td><td>29911.915</td><td>0.00002</td></tr><tr><td>49</td><td>&quot;湘财&quot;</td><td>2022-10-25</td><td>&quot;09:30:39&quot;</td><td>&quot;002996&quot;</td><td>&quot;顺博合金&quot;</td><td>&quot;卖出&quot;</td><td>13.64</td><td>100.0</td><td>1364.0</td><td>0.22</td><td>1.36</td><td>0.01</td><td>0.0</td><td>1362.42</td><td>0.000161</td><td>0.000997</td><td>0.000007</td><td>13.57</td><td>13.93</td><td>13.26</td><td>13.82</td><td>13.64</td><td>0.18</td><td>1.3196</td><td>38258.22</td><td>52122.119</td><td>0.000026</td></tr><tr><td>235</td><td>&quot;湘财&quot;</td><td>2023-04-25</td><td>&quot;13:35:07&quot;</td><td>&quot;601166&quot;</td><td>&quot;兴业银行&quot;</td><td>&quot;买入&quot;</td><td>17.15</td><td>3000.0</td><td>51450.0</td><td>8.23</td><td>0.0</td><td>0.51</td><td>0.0</td><td>-51458.74</td><td>0.00016</td><td>0.0</td><td>0.00001</td><td>16.96</td><td>17.29</td><td>16.96</td><td>17.27</td><td>16.93</td><td>0.34</td><td>2.0083</td><td>995234.22</td><td>1.7071e6</td><td>0.00003</td></tr><tr><td>46</td><td>&quot;湘财&quot;</td><td>2022-10-25</td><td>&quot;09:25:00&quot;</td><td>&quot;002998&quot;</td><td>&quot;优彩资源&quot;</td><td>&quot;卖出&quot;</td><td>6.73</td><td>100.0</td><td>673.0</td><td>0.11</td><td>0.67</td><td>0.01</td><td>0.0</td><td>672.22</td><td>0.000163</td><td>0.000996</td><td>0.000015</td><td>6.73</td><td>6.73</td><td>6.56</td><td>6.64</td><td>6.72</td><td>-0.08</td><td>-1.1905</td><td>16145.0</td><td>10687.614</td><td>0.000062</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>30</td><td>&quot;湘财&quot;</td><td>2022-10-11</td><td>&quot;09:41:06&quot;</td><td>&quot;600231&quot;</td><td>&quot;凌钢股份&quot;</td><td>&quot;买入&quot;</td><td>2.2</td><td>62700.0</td><td>137940.0</td><td>22.07</td><td>0.0</td><td>1.29</td><td>0.0</td><td>-137963.36</td><td>0.00016</td><td>0.0</td><td>0.000009</td><td>2.21</td><td>2.22</td><td>2.15</td><td>2.2</td><td>2.21</td><td>-0.01</td><td>-0.4525</td><td>61371.2</td><td>13386.328</td><td>0.010217</td></tr><tr><td>3</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:36:30&quot;</td><td>&quot;600894&quot;</td><td>&quot;广日股份&quot;</td><td>&quot;买入&quot;</td><td>6.54</td><td>21400.0</td><td>139956.0</td><td>22.39</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-139979.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td><td>6.57</td><td>6.57</td><td>6.49</td><td>6.51</td><td>6.57</td><td>-0.06</td><td>-0.9132</td><td>20737.5</td><td>13537.134</td><td>0.010319</td></tr><tr><td>142</td><td>&quot;湘财&quot;</td><td>2023-02-21</td><td>&quot;09:37:16&quot;</td><td>&quot;300385&quot;</td><td>&quot;雪浪环境&quot;</td><td>&quot;买入&quot;</td><td>6.5293</td><td>11500.0</td><td>75087.0</td><td>12.01</td><td>0.0</td><td>0.77</td><td>0.0</td><td>-75099.01</td><td>0.00016</td><td>0.0</td><td>0.00001</td><td>6.53</td><td>6.59</td><td>6.45</td><td>6.52</td><td>6.5</td><td>0.02</td><td>0.3077</td><td>11136.0</td><td>7250.686</td><td>0.010327</td></tr><tr><td>152</td><td>&quot;湘财&quot;</td><td>2023-03-01</td><td>&quot;09:31:46&quot;</td><td>&quot;688069&quot;</td><td>&quot;德林海环保&quot;</td><td>&quot;卖出&quot;</td><td>28.2838</td><td>2600.0</td><td>73538.0</td><td>11.77</td><td>73.58</td><td>0.77</td><td>0.0</td><td>73451.88</td><td>0.00016</td><td>0.001001</td><td>0.00001</td><td>28.52</td><td>28.86</td><td>28.27</td><td>28.67</td><td>28.54</td><td>0.13</td><td>0.4555</td><td>2416.06</td><td>6897.976</td><td>0.010761</td></tr><tr><td>27</td><td>&quot;湘财&quot;</td><td>2022-10-11</td><td>&quot;09:37:32&quot;</td><td>&quot;600261&quot;</td><td>&quot;阳光照明&quot;</td><td>&quot;买入&quot;</td><td>3.1134</td><td>44600.0</td><td>138857.0</td><td>22.22</td><td>0.0</td><td>1.33</td><td>0.0</td><td>-138880.55</td><td>0.00016</td><td>0.0</td><td>0.00001</td><td>3.14</td><td>3.15</td><td>3.06</td><td>3.13</td><td>3.11</td><td>0.02</td><td>0.6431</td><td>41397.03</td><td>12872.499</td><td>0.010774</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (358, 28)\n",
       "┌──────┬──────┬────────────┬──────────┬───┬─────────┬───────────┬───────────┬──────────┐\n",
       "│ 序号 ┆ 券商 ┆ 交易日期   ┆ 交易时间 ┆ … ┆ pct_chg ┆ vol       ┆ amount    ┆ vlratio  │\n",
       "│ ---  ┆ ---  ┆ ---        ┆ ---      ┆   ┆ ---     ┆ ---       ┆ ---       ┆ ---      │\n",
       "│ u32  ┆ str  ┆ date       ┆ str      ┆   ┆ f64     ┆ f64       ┆ f64       ┆ f64      │\n",
       "╞══════╪══════╪════════════╪══════════╪═══╪═════════╪═══════════╪═══════════╪══════════╡\n",
       "│ 237  ┆ 湘财 ┆ 2023-05-09 ┆ 09:33:16 ┆ … ┆ -0.3971 ┆ 1.6276e6  ┆ 2.8810e6  ┆ 0.000018 │\n",
       "│ 48   ┆ 湘财 ┆ 2022-10-25 ┆ 09:30:19 ┆ … ┆ -2.2581 ┆ 49219.99  ┆ 29911.915 ┆ 0.00002  │\n",
       "│ 49   ┆ 湘财 ┆ 2022-10-25 ┆ 09:30:39 ┆ … ┆ 1.3196  ┆ 38258.22  ┆ 52122.119 ┆ 0.000026 │\n",
       "│ 235  ┆ 湘财 ┆ 2023-04-25 ┆ 13:35:07 ┆ … ┆ 2.0083  ┆ 995234.22 ┆ 1.7071e6  ┆ 0.00003  │\n",
       "│ 46   ┆ 湘财 ┆ 2022-10-25 ┆ 09:25:00 ┆ … ┆ -1.1905 ┆ 16145.0   ┆ 10687.614 ┆ 0.000062 │\n",
       "│ …    ┆ …    ┆ …          ┆ …        ┆ … ┆ …       ┆ …         ┆ …         ┆ …        │\n",
       "│ 30   ┆ 湘财 ┆ 2022-10-11 ┆ 09:41:06 ┆ … ┆ -0.4525 ┆ 61371.2   ┆ 13386.328 ┆ 0.010217 │\n",
       "│ 3    ┆ 湘财 ┆ 2022-07-11 ┆ 09:36:30 ┆ … ┆ -0.9132 ┆ 20737.5   ┆ 13537.134 ┆ 0.010319 │\n",
       "│ 142  ┆ 湘财 ┆ 2023-02-21 ┆ 09:37:16 ┆ … ┆ 0.3077  ┆ 11136.0   ┆ 7250.686  ┆ 0.010327 │\n",
       "│ 152  ┆ 湘财 ┆ 2023-03-01 ┆ 09:31:46 ┆ … ┆ 0.4555  ┆ 2416.06   ┆ 6897.976  ┆ 0.010761 │\n",
       "│ 27   ┆ 湘财 ┆ 2022-10-11 ┆ 09:37:32 ┆ … ┆ 0.6431  ┆ 41397.03  ┆ 12872.499 ┆ 0.010774 │\n",
       "└──────┴──────┴────────────┴──────────┴───┴─────────┴───────────┴───────────┴──────────┘"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d1.join(\n",
    "    hq, left_on=[\"交易日期\", \"证券代码\"], right_on=[\"trade_date\", \"ts_code\"], how=\"left\"\n",
    ").with_columns(\n",
    "    vlratio=pl.col(\"成交数量\") / 100 / pl.col(\"vol\"),\n",
    ").sort(\"vlratio\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "98a9b5c0-aa30-4937-aec4-0eeb47778922",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (72_671, 19)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>日期</th><th>证券代码</th><th>序号</th><th>券商</th><th>交易时间</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>发生金额</th><th>手续费率</th><th>印花税率</th><th>过户费率</th><th>结余数量</th></tr><tr><td>date</td><td>str</td><td>u32</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>2022-07-11</td><td>&quot;000096&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2022-07-11</td><td>&quot;000532&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2022-07-11</td><td>&quot;000559&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2022-07-11</td><td>&quot;000599&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2022-07-11</td><td>&quot;000655&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>2023-10-31</td><td>&quot;688299&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2023-10-31</td><td>&quot;688321&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2023-10-31</td><td>&quot;688360&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2023-10-31</td><td>&quot;688393&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2023-10-31</td><td>&quot;688660&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>20200.0</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (72_671, 19)\n",
       "┌────────────┬──────────┬──────┬──────┬───┬──────────┬──────────┬──────────┬──────────┐\n",
       "│ 日期       ┆ 证券代码 ┆ 序号 ┆ 券商 ┆ … ┆ 手续费率 ┆ 印花税率 ┆ 过户费率 ┆ 结余数量 │\n",
       "│ ---        ┆ ---      ┆ ---  ┆ ---  ┆   ┆ ---      ┆ ---      ┆ ---      ┆ ---      │\n",
       "│ date       ┆ str      ┆ u32  ┆ str  ┆   ┆ f64      ┆ f64      ┆ f64      ┆ f64      │\n",
       "╞════════════╪══════════╪══════╪══════╪═══╪══════════╪══════════╪══════════╪══════════╡\n",
       "│ 2022-07-11 ┆ 000096   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2022-07-11 ┆ 000532   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2022-07-11 ┆ 000559   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2022-07-11 ┆ 000599   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2022-07-11 ┆ 000655   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ …          ┆ …        ┆ …    ┆ …    ┆ … ┆ …        ┆ …        ┆ …        ┆ …        │\n",
       "│ 2023-10-31 ┆ 688299   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2023-10-31 ┆ 688321   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2023-10-31 ┆ 688360   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2023-10-31 ┆ 688393   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2023-10-31 ┆ 688660   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 20200.0  │\n",
       "└────────────┴──────────┴──────┴──────┴───┴──────────┴──────────┴──────────┴──────────┘"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d3 = (\n",
    "    k.join(\n",
    "        d1, left_on=[\"日期\", \"证券代码\"], right_on=[\"交易日期\", \"证券代码\"], how=\"left\"\n",
    "    )\n",
    "    .sort(\"日期\", \"证券代码\")\n",
    "    .with_columns(\n",
    "        结余数量=(\n",
    "            pl.when(pl.col(\"买卖标志\") == \"买入\")\n",
    "            .then(pl.col(\"成交数量\"))\n",
    "            .when(pl.col(\"买卖标志\") == \"卖出\")\n",
    "            .then(-pl.col(\"成交数量\"))\n",
    "            .otherwise(0)\n",
    "            .cum_sum()\n",
    "            .over(\"证券代码\")\n",
    "        ),\n",
    "    )\n",
    ")\n",
    "d3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "6380f45c-1d43-40f8-a38b-d5f1432853f0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (478, 6)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>日期</th><th>持股市值</th><th>发生金额</th><th>转账金额</th><th>现金余额</th><th>总资产</th></tr><tr><td>date</td><td>f64</td><td>f64</td><td>i32</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>2022-07-11</td><td>703040.0</td><td>-699551.12</td><td>1000000</td><td>300448.88</td><td>1.0035e6</td></tr><tr><td>2022-07-12</td><td>707714.0</td><td>0.0</td><td>0</td><td>300448.88</td><td>1.0082e6</td></tr><tr><td>2022-07-13</td><td>713855.0</td><td>0.0</td><td>0</td><td>300448.88</td><td>1.0143e6</td></tr><tr><td>2022-07-14</td><td>710079.0</td><td>0.0</td><td>0</td><td>300448.88</td><td>1.0105e6</td></tr><tr><td>2022-07-15</td><td>692377.0</td><td>0.0</td><td>0</td><td>300448.88</td><td>992825.88</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>2023-10-27</td><td>571195.0</td><td>0.0</td><td>0</td><td>510845.96</td><td>1.0820e6</td></tr><tr><td>2023-10-28</td><td>571195.0</td><td>0.0</td><td>0</td><td>510845.96</td><td>1.0820e6</td></tr><tr><td>2023-10-29</td><td>571195.0</td><td>0.0</td><td>0</td><td>510845.96</td><td>1.0820e6</td></tr><tr><td>2023-10-30</td><td>686345.0</td><td>-94884.93</td><td>0</td><td>415961.03</td><td>1.1023e6</td></tr><tr><td>2023-10-31</td><td>748947.0</td><td>6490.78</td><td>0</td><td>422451.81</td><td>1.1714e6</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (478, 6)\n",
       "┌────────────┬──────────┬────────────┬──────────┬───────────┬───────────┐\n",
       "│ 日期       ┆ 持股市值 ┆ 发生金额   ┆ 转账金额 ┆ 现金余额  ┆ 总资产    │\n",
       "│ ---        ┆ ---      ┆ ---        ┆ ---      ┆ ---       ┆ ---       │\n",
       "│ date       ┆ f64      ┆ f64        ┆ i32      ┆ f64       ┆ f64       │\n",
       "╞════════════╪══════════╪════════════╪══════════╪═══════════╪═══════════╡\n",
       "│ 2022-07-11 ┆ 703040.0 ┆ -699551.12 ┆ 1000000  ┆ 300448.88 ┆ 1.0035e6  │\n",
       "│ 2022-07-12 ┆ 707714.0 ┆ 0.0        ┆ 0        ┆ 300448.88 ┆ 1.0082e6  │\n",
       "│ 2022-07-13 ┆ 713855.0 ┆ 0.0        ┆ 0        ┆ 300448.88 ┆ 1.0143e6  │\n",
       "│ 2022-07-14 ┆ 710079.0 ┆ 0.0        ┆ 0        ┆ 300448.88 ┆ 1.0105e6  │\n",
       "│ 2022-07-15 ┆ 692377.0 ┆ 0.0        ┆ 0        ┆ 300448.88 ┆ 992825.88 │\n",
       "│ …          ┆ …        ┆ …          ┆ …        ┆ …         ┆ …         │\n",
       "│ 2023-10-27 ┆ 571195.0 ┆ 0.0        ┆ 0        ┆ 510845.96 ┆ 1.0820e6  │\n",
       "│ 2023-10-28 ┆ 571195.0 ┆ 0.0        ┆ 0        ┆ 510845.96 ┆ 1.0820e6  │\n",
       "│ 2023-10-29 ┆ 571195.0 ┆ 0.0        ┆ 0        ┆ 510845.96 ┆ 1.0820e6  │\n",
       "│ 2023-10-30 ┆ 686345.0 ┆ -94884.93  ┆ 0        ┆ 415961.03 ┆ 1.1023e6  │\n",
       "│ 2023-10-31 ┆ 748947.0 ┆ 6490.78    ┆ 0        ┆ 422451.81 ┆ 1.1714e6  │\n",
       "└────────────┴──────────┴────────────┴──────────┴───────────┴───────────┘"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d4 = (\n",
    "    (\n",
    "        d3.join(\n",
    "            hq,\n",
    "            left_on=[\"日期\", \"证券代码\"],\n",
    "            right_on=[\"trade_date\", \"ts_code\"],\n",
    "            how=\"left\",\n",
    "        )\n",
    "        .sort(\"证券代码\", \"日期\")\n",
    "        .with_columns(\n",
    "            close=pl.col(\"close\").fill_null(strategy=\"forward\").over(\"证券代码\")\n",
    "        )\n",
    "        .with_columns(持股市值=pl.col(\"结余数量\") * pl.col(\"close\"))\n",
    "        .group_by(\"日期\")\n",
    "        .agg(\n",
    "            pl.col(\"持股市值\").sum(),\n",
    "            pl.col(\"发生金额\").sum(),\n",
    "        )\n",
    "    )\n",
    "    .sort(\"日期\")\n",
    "    .with_columns(\n",
    "        转账金额=pl.when(pl.int_range(0, pl.len()) == 0).then(100_0000).otherwise(0),\n",
    "    )\n",
    "    .with_columns(现金余额=(pl.col(\"转账金额\") + pl.col(\"发生金额\")).cum_sum())\n",
    "    .with_columns(总资产=pl.col(\"持股市值\") + pl.col(\"现金余额\"))\n",
    ")\n",
    "d4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "550e2c1a-486b-4230-908f-9f5135b4b7a5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a31991c75d4145b18823ddd8c8586ab7",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "PerspectiveWidget(binding_mode='server', columns=['日期', '持股市值', '发生金额', '转账金额', '现金余额', '总资产'], table_name='0.…"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PerspectiveWidget(d4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "cbab7a64-d371-49f6-9178-7f5c07c90043",
   "metadata": {},
   "outputs": [],
   "source": [
    "ihq = pro.index_daily(\n",
    "    ts_code=\"000300.SH\",\n",
    "    start_date=format(start_date, \"%Y%m%d\"),\n",
    "    end_date=format(end_date, \"%Y%m%d\"),\n",
    "    fields=\"ts_code,trade_date,pct_chg\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "60dec0a7-5ca9-4331-8974-34b0c73f4cfc",
   "metadata": {},
   "outputs": [],
   "source": [
    "pl.from_pandas(ihq).write_parquet(\"index_daily.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "72a0809f-daa6-4693-9642-8a024b4e4c9a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (318, 5)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ts_code</th><th>trade_date</th><th>pct_chg</th><th>car</th><th>沪深300</th></tr><tr><td>str</td><td>date</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;000300.SH&quot;</td><td>2022-07-11</td><td>0.983254</td><td>0.983254</td><td>983254.0</td></tr><tr><td>&quot;000300.SH&quot;</td><td>2022-07-12</td><td>0.990585</td><td>0.973997</td><td>973996.66359</td></tr><tr><td>&quot;000300.SH&quot;</td><td>2022-07-13</td><td>1.001818</td><td>0.975767</td><td>975767.389524</td></tr><tr><td>&quot;000300.SH&quot;</td><td>2022-07-14</td><td>1.000142</td><td>0.975906</td><td>975905.948494</td></tr><tr><td>&quot;000300.SH&quot;</td><td>2022-07-15</td><td>0.982983</td><td>0.959299</td><td>959298.956968</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>&quot;000300.SH&quot;</td><td>2023-10-25</td><td>1.004969</td><td>0.791288</td><td>791288.453778</td></tr><tr><td>&quot;000300.SH&quot;</td><td>2023-10-26</td><td>1.002764</td><td>0.793476</td><td>793475.575065</td></tr><tr><td>&quot;000300.SH&quot;</td><td>2023-10-27</td><td>1.013727</td><td>0.804368</td><td>804367.614284</td></tr><tr><td>&quot;000300.SH&quot;</td><td>2023-10-30</td><td>1.006003</td><td>0.809196</td><td>809196.233072</td></tr><tr><td>&quot;000300.SH&quot;</td><td>2023-10-31</td><td>0.996856</td><td>0.806652</td><td>806652.120115</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (318, 5)\n",
       "┌───────────┬────────────┬──────────┬──────────┬───────────────┐\n",
       "│ ts_code   ┆ trade_date ┆ pct_chg  ┆ car      ┆ 沪深300       │\n",
       "│ ---       ┆ ---        ┆ ---      ┆ ---      ┆ ---           │\n",
       "│ str       ┆ date       ┆ f64      ┆ f64      ┆ f64           │\n",
       "╞═══════════╪════════════╪══════════╪══════════╪═══════════════╡\n",
       "│ 000300.SH ┆ 2022-07-11 ┆ 0.983254 ┆ 0.983254 ┆ 983254.0      │\n",
       "│ 000300.SH ┆ 2022-07-12 ┆ 0.990585 ┆ 0.973997 ┆ 973996.66359  │\n",
       "│ 000300.SH ┆ 2022-07-13 ┆ 1.001818 ┆ 0.975767 ┆ 975767.389524 │\n",
       "│ 000300.SH ┆ 2022-07-14 ┆ 1.000142 ┆ 0.975906 ┆ 975905.948494 │\n",
       "│ 000300.SH ┆ 2022-07-15 ┆ 0.982983 ┆ 0.959299 ┆ 959298.956968 │\n",
       "│ …         ┆ …          ┆ …        ┆ …        ┆ …             │\n",
       "│ 000300.SH ┆ 2023-10-25 ┆ 1.004969 ┆ 0.791288 ┆ 791288.453778 │\n",
       "│ 000300.SH ┆ 2023-10-26 ┆ 1.002764 ┆ 0.793476 ┆ 793475.575065 │\n",
       "│ 000300.SH ┆ 2023-10-27 ┆ 1.013727 ┆ 0.804368 ┆ 804367.614284 │\n",
       "│ 000300.SH ┆ 2023-10-30 ┆ 1.006003 ┆ 0.809196 ┆ 809196.233072 │\n",
       "│ 000300.SH ┆ 2023-10-31 ┆ 0.996856 ┆ 0.806652 ┆ 806652.120115 │\n",
       "└───────────┴────────────┴──────────┴──────────┴───────────────┘"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ihq = pl.read_parquet(\"index_daily.parquet\")\n",
    "ihq = (\n",
    "    ihq.with_columns(\n",
    "        pl.col(\"trade_date\").str.to_date(\"%Y%m%d\"),\n",
    "        pl.col(\"pct_chg\") / 100 + 1,\n",
    "    )\n",
    "    .sort(\"trade_date\")\n",
    "    .with_columns(\n",
    "        car=pl.col(\"pct_chg\").cum_prod(),\n",
    "    )\n",
    "    .with_columns(沪深300=pl.col(\"car\") * 100_0000)\n",
    ")\n",
    "ihq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "30e93003-fbbd-4bf5-866f-a818be5b1c74",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "2cb0bfa9cd2b40218a60c3b3f09c32e9",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "PerspectiveWidget(binding_mode='server', columns=['ts_code', 'trade_date', 'pct_chg', 'car', '沪深300'], table_n…"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PerspectiveWidget(ihq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "id": "54527e6a-0aba-44c1-b030-28d0347149fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "d5 = d4.join(ihq, left_on=\"日期\", right_on=\"trade_date\")\n",
    "d5 = d5.unpivot(\n",
    "    on=[\"总资产\", \"沪深300\"], index=\"日期\", variable_name=\"资产类型\", value_name=\"财富\"\n",
    ")\n",
    "d5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "id": "3951d116-a8d6-4168-8777-a55fab22ffb2",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "2c13ae74dbf244de8964086aa60a0e0a",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "PerspectiveWidget(binding_mode='server', columns=['日期', '资产类型', '财富'], table_name='0.13441640004178046', theme…"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PerspectiveWidget(d5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "463b63db-cb05-4a1d-b635-0e0e22448eca",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "562f035f-24d7-42f2-9ec5-7ecfa136c1cb",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
